/*
 * Copyright 2008 Carnegie Mellon University
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package edu.cmu.lti.sepia.task.update;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import edu.cmu.lti.sepia.data.db.Query;
import edu.cmu.lti.sepia.data.db.SQL;
import edu.cmu.lti.sepia.data.db.TaskDAO;
import edu.cmu.lti.sepia.infra.topic.model.Task;
import edu.cmu.lti.sepia.util.ExceptionUtil;

public class Updater {

  // Read version info and check version difference...?
  //-> No, the very initial version of SEPIA doesn't store version in the prop file 
  
  public static String doUpdates( SQL sql ) {
    return update1(sql);
  }
  
  // Detect if the DB schema is compatible with v1.03
  public static String update1( SQL sql ) {
    StringBuilder sb = new StringBuilder();
    try {
      String query = Query.generate( "SELECT td_labels FROM @S@.task", "" );
      PreparedStatement ps = sql.getConnection().prepareStatement(query);
      ResultSet rs = sql.executeSelect(ps);
      if (rs==null) {
        sb.append("<strong>The database does not have the up-to-date schema.</strong><br><br>\n");
        sb.append("SQLs executed:<ul>");
        sb.append(runSQL(sql, "ALTER TABLE `@S@`.`task` DROP INDEX `task_name_short`", ""));
        sb.append(runSQL(sql, "ALTER TABLE `@S@`.`task` ADD `td_labels` " +
                "TINYTEXT character set utf8 collate utf8_bin NOT NULL AFTER `types`", ""));
        List<Task> tasks = TaskDAO.getTasks(sql);
        for ( Task t : tasks ) {
          sb.append(runSQL(sql, "ALTER TABLE `@T@`.`answer` ADD `original_answer_sentence` " +
                  "TEXT character set utf8 collate utf8_bin NOT NULL AFTER `answer_sentence`", t.getTaskNameShort()));
        }
        for ( Task t : tasks ) {
          sb.append(runSQL(sql, "ALTER TABLE `@T@`.`answer` ADD `label` " +
                  "VARCHAR(100) character set utf8 collate utf8_bin NOT NULL default '' AFTER `original_answer_sentence`",
                  t.getTaskNameShort()));
        }
        sb.append("</ul>\n");
      } else {
        sb.append("The database has the up-to-date schema.<br><br>\n");
      }
      ps.close();
      
    } catch (Exception e) {
      sb.append("<br>--Exception--<br>\n"+ExceptionUtil.exceptionToString(e));
    }
    String message = sb.toString();
    if (message.indexOf("Exception")!=-1) {
      return message+"<hr>Database update failed. Please execute SQLs by yourself.";
    } else {
      return message;
    }
    
  }
  
  public static String runSQL( SQL sql, String statement, String taskNameShort ) {
    StringBuilder sb = new StringBuilder();
    try {
      String q = Query.generate( statement, taskNameShort );
      sb.append("<li><code>"+q+"</code>\n");
      String exception = sql.execute(q);
      if (exception.length()>0) sb.append("<ul><li>"+exception+"</li></ul>");
    } catch (Exception e) {
      sb.append("<ul><li>"+ExceptionUtil.exceptionToString(e)+"</li></ul>");
    }
    return sb.toString();
  }
  
}
